import json
import mysql.connector
import lark_oapi as lark
from lark_oapi.api.bitable.v1 import *
import datetime
import logging

# 配置日志
logging.basicConfig(
    level=logging.DEBUG,
    format='%(asctime)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)

def create_feishu_client():
    """创建飞书客户端"""
    try:
        client = lark.Client.builder() \
            .app_id("cli_a78ca004593f500d") \
            .app_secret("kXoSVSC8qCfDZusty52uCdbnAQnl5AF4") \
            .log_level(lark.LogLevel.DEBUG) \
            .build()
        logger.info("飞书客户端创建成功")
        return client
    except Exception as e:
        logger.error(f"创建飞书客户端失败: {str(e)}")
        raise

def create_table_request():
    """创建表格请求"""
    try:
        request = SearchAppTableRecordRequest.builder() \
            .app_token("TQUXbGqKlaCpAEskdJrcEGbGn2b") \
            .table_id("tbli5VTmAnNxLqjV") \
            .request_body(SearchAppTableRecordRequestBody.builder()
                .view_id("vewpJn2zpM")
                .field_names(["归属模块", "提出人", "责任方", "责任人", "计划关闭时间", "提出时间"])
                .automatic_fields(False)
                .build()) \
            .build()
        logger.info("表格请求创建成功")
        return request
    except Exception as e:
        logger.error(f"创建表格请求失败: {str(e)}")
        raise

def connect_database():
    """连接数据库"""
    try:
        cnx = mysql.connector.connect(
            user='root',
            password='password',
            host='10.103.21.193',
            database='test'
        )
        logger.info("数据库连接成功")
        return cnx
    except mysql.connector.Error as err:
        logger.error(f"数据库连接失败: {err}")
        raise

def process_table_data(response):
    """处理表格数据"""
    if not response.success():
        logger.error(f"获取表格数据失败: {response.msg}")
        return

    try:
        data_items = response.data.items
        logger.info(f"成功获取 {len(data_items)} 条数据")
        
        for item in data_items:
            process_single_item(item)
    except Exception as e:
        logger.error(f"处理表格数据失败: {str(e)}")
        raise

def process_single_item(item):
    """处理单条数据"""
    try:
        tester = item.fields.get("提出人")
        respter = item.fields.get("责任人")
        re_time = item.fields.get("提出时间")
        mode = item.fields.get("归属模块")
        
        # 处理测试人员信息
        name_str = extract_name(tester) if tester else ''
        
        # 处理时间信息
        time_list = convert_timestamp_to_date(re_time) if re_time else "none"
        
        # 处理模块信息
        mode_list = process_mode(mode) if mode else ''
        
        # 处理责任人信息
        respter_name_str = extract_name(respter) if respter else 'None'
        
        # 插入数据库
        insert_to_database(name_str, mode_list, time_list, respter_name_str)
        
    except Exception as e:
        logger.error(f"处理单条数据失败: {str(e)}")
        raise

def extract_name(data):
    """提取名称信息"""
    if not data:
        return ''
    try:
        data_str = json.dumps(data)
        data = json.loads(data_str)
        name = [d['name'] for d in data]
        return name[0] if name else ''
    except Exception as e:
        logger.error(f"提取名称失败: {str(e)}")
        return ''

def process_mode(mode):
    """处理模块信息"""
    try:
        data_str = json.dumps(mode)
        return json.loads(data_str)
    except Exception as e:
        logger.error(f"处理模块信息失败: {str(e)}")
        return ''

def insert_to_database(name_str, mode_list, time_list, respter_name_str):
    """插入数据到数据库"""
    try:
        cnx = connect_database()
        cursor = cnx.cursor()
        
        sqlInsert = "INSERT INTO 车身集成缺陷改善统计表(测试人员,模块,提出时间,责任人) VALUES (%s,%s,%s,%s)"
        data_insert = (name_str, mode_list, time_list, respter_name_str)
        
        cursor.execute(sqlInsert, data_insert)
        cnx.commit()
        logger.info(f"成功插入数据: {data_insert}")
        
    except mysql.connector.Error as err:
        logger.error(f"数据库操作失败: {err}")
        raise
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'cnx' in locals():
            cnx.close()

def convert_timestamp_to_date(ms_timestamp):
    """将时间戳转换为日期"""
    try:
        dt_object = datetime.datetime.fromtimestamp(ms_timestamp/1000)
        formatted_date = dt_object.strftime("%Y-%m-%d")
        return formatted_date
    except Exception as e:
        logger.error(f"时间戳转换失败: {str(e)}")
        return "none"

def main():
    try:
        # 创建飞书客户端
        client = create_feishu_client()
        
        # 创建表格请求
        request = create_table_request()
        
        # 发送请求
        response = client.bitable.v1.app_table_record.search(request)
        
        # 处理响应数据
        process_table_data(response)
        
        logger.info("数据同步完成")
        
    except Exception as e:
        logger.error(f"程序执行失败: {str(e)}")
        raise

if __name__ == "__main__":
    main()